# import pymysql.cursors
import pymysql

from logs.user_log import UserLog

log = UserLog().get_log()


class ConfigDB:
    # 除了port以外，其他参数都必须是str
    host = '127.0.0.1'
    port = 3306
    user = 'root'
    password = '123456'
    db_name = 'api_test_case'
    table_name_01 = 'api_case_01'
    table_name_zxyj = 'case_zxyj'
    table_name_remine = 'case_remine'
    table_name = table_name_01

config = ConfigDB()


class MysqlDB:
    def __init__(self):
        # 初始化数据库连接
        try:
            # 连接 database
            self.connection = pymysql.connect(host=config.host,
                                              port=int(config.port),
                                              user=config.user,
                                              password=config.password,
                                              db=config.db_name,
                                              charset='utf8mb4')
                                              # cursorclass=pymysql.cursors.DictCursor
        except pymysql.err.OperationalError as e:
            print("Mysql 连接错误 %d: %s" % (e.args[0], e.args[1]))

    def get_col_name(self):
        sql_col = "SELECT COLUMN_NAME  FROM information_schema.columns WHERE table_name='case_zxyj';"
        # sql_col = 'SELECT * FROM case_zxyj WHERE id = 2'
        # print(sql_col)
        cursor = self.connection.cursor()
        cursor.execute(sql_col)
        list_col = []; col_tuple = cursor.fetchall()
        for i in range(17):
            one = col_tuple[i][0]
            list_col.append(one)
        # self.connection.commit()
        # print(list_col)
        return list_col

    def get_value_list(self, case_id, table_name=None):
        if table_name is None:
            table_name = config.table_name_zxyj
        col_name = 'case_id'
        sql_value = "SELECT * FROM {} WHERE {} = '{}';".format(table_name, col_name, case_id)
        cursor = self.connection.cursor()
        cursor.execute(sql_value)
        list_value = cursor.fetchone()
        # self.connection.commit()
        # print(list_value)
        return list_value


    def get_case_dict_by_CaseID(self, case_id, table_name=None):
        key_list = self.get_col_name()
        value_list = self.get_value_list(case_id, table_name)
        case_dict = {}
        i = 0
        len_1 = len(key_list)
        while i < len_1:
            case_dict[key_list[i]] = value_list[i]
            i += 1
        # print(case_dict)
        return case_dict

    # 关闭数据库连接
    def close(self):
        log.info('关闭数据库连接')
        try:
            self.connection.close()
        except pymysql.err.Error:
            pass

    # 重新连接
    def reConnect(self):
        try:
            self.connection.ping()
        except:
            self.connection()

    def load_data(self, table_name=None, col_name='is_run', value='yes',
                  limit_num=10, between=None, sql_id=None, run_fail=None):
        if table_name is None:
            table_name = config.table_name
        if run_fail:
            col_name = 'result'
            value = '通过'
            sql_value = "SELECT * FROM {} WHERE {} != '{}';".format(
                table_name, col_name, value)
        elif between:
            bet_01, bet_02 = between
            sql_value = "SELECT * FROM {} WHERE {} = '{}' and ID between {} and {};".format(
                table_name, col_name, value, bet_01, bet_02)
        elif sql_id:
            sql_value = "SELECT * FROM {} WHERE {} = '{}' and ID = {};".format(
                table_name, col_name, value, sql_id)
        else:
            sql_value = "SELECT * FROM {} WHERE {} = '{}' LIMIT {};".format(
                table_name, col_name, value, limit_num)
        cursor = self.connection.cursor()
        cursor.execute(sql_value)
        case_all = cursor.fetchall()
        # print(sql_value);print(case_all)
        key_list = self.get_col_name()
        for i_value_list in case_all:
            case_dict = dict(zip(key_list,i_value_list))
            yield case_dict

    def update_case(self, sql_id=None, result=None, clear=False):
        if clear:
            result = 'null'
            sql = 'UPDATE case_zxyj set result = {};'.format(result, sql_id)
        else:
            if result and sql_id:
                result = '通过'
            else:
                result = '失败'
            sql = 'UPDATE case_zxyj set result = "{}" WHERE id = "{}";'.format(result, sql_id)
        print(sql)
        cursor = self.connection.cursor()
        cursor.execute(sql)
        self.connection.commit()

if __name__ == '__main__':
    pass
    # print('hahaha')
    d01 = MysqlDB()
    d01.load_data(table_name='case_zxyj', fail=True)
    d01.close()



